﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

using ZhangBY.Config;
using ZhangBY.Common;
using ZhangBY.Entity;

namespace ZhangBY.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {
        
        /// <summary>
        /// 发布合作
        /// </summary>
        /// <returns></returns>
        public int InsertCooperate(CooperateInfo cooperateInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@UserId",(DbType)SqlDbType.NVarChar,60,cooperateInfo.UserUid),
                                      DbHelper.MakeInParam("@ProjectId",(DbType)SqlDbType.NVarChar,60,cooperateInfo.ProjectId),
                                      DbHelper.MakeInParam("@ProjectName",(DbType)SqlDbType.NChar,20,cooperateInfo.ProjectName),
                                      DbHelper.MakeInParam("@ProjectType",(DbType)SqlDbType.NChar,10,cooperateInfo.ProjectType),
                                      DbHelper.MakeInParam("@Key",(DbType)SqlDbType.NVarChar,100,cooperateInfo.Key),
                                      DbHelper.MakeInParam("@Title",(DbType)SqlDbType.NChar,30,cooperateInfo.Title),
                                      DbHelper.MakeInParam("@Description",(DbType)SqlDbType.NVarChar,200,cooperateInfo.Description),                                     
                                      DbHelper.MakeInParam("@Area",(DbType)SqlDbType.NChar,40,cooperateInfo.Area),
                                      DbHelper.MakeInParam("@Contact",(DbType)SqlDbType.NChar,10,cooperateInfo.Contact),
                                      DbHelper.MakeInParam("@Phone",(DbType)SqlDbType.NVarChar,40,cooperateInfo.Phone),
                                      DbHelper.MakeInParam("@Agency",(DbType)SqlDbType.NChar,10,cooperateInfo.Agency),
                                      DbHelper.MakeInParam("@Audit",(DbType)SqlDbType.Bit,4,cooperateInfo.Audit),
                                      DbHelper.MakeInParam("@Maturity",(DbType)SqlDbType.NChar,cooperateInfo.Maturity.Length,cooperateInfo.Maturity),
                                      DbHelper.MakeInParam("@Date",(DbType)SqlDbType.NChar,cooperateInfo.Date.Length,cooperateInfo.Date)
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("INSERT INTO [ZhangBY].[dbo].[{0}cooperate] ", BaseConfigs.GetTablePrefix);
            commantText.Append("([userid],[projectid],[title],[projectname],[projecttype],[key],[description],[phone],[contact],[area],[Agency],[audit],[maturity],[date])");
            commantText.Append("VALUES(@userid,@projectid,@title,@projectname,@projecttype,@key,@description,@phone,@contact,@area,@agency,@audit,@maturity,@date)");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
        /// <summary>
        /// 根据用户ID和合作项目ID更新合作信息
        /// </summary>
        /// <param name="cooperateInfo"></param>
        /// <returns></returns>
        public int UpdateCooperateByProjectIdAndUserID(CooperateInfo cooperateInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@UserId",(DbType)SqlDbType.NVarChar,60,cooperateInfo.UserUid),
                                      DbHelper.MakeInParam("@ProjectId",(DbType)SqlDbType.NVarChar,60,cooperateInfo.ProjectId),
                                      DbHelper.MakeInParam("@ProjectName",(DbType)SqlDbType.NChar,20,cooperateInfo.ProjectName),
                                      DbHelper.MakeInParam("@ProjectType",(DbType)SqlDbType.NChar,10,cooperateInfo.ProjectType),
                                      DbHelper.MakeInParam("@Key",(DbType)SqlDbType.NVarChar,100,cooperateInfo.Key),
                                      DbHelper.MakeInParam("@Title",(DbType)SqlDbType.NChar,30,cooperateInfo.Title),
                                      DbHelper.MakeInParam("@Description",(DbType)SqlDbType.NVarChar,200,cooperateInfo.Description),                                     
                                      DbHelper.MakeInParam("@Area",(DbType)SqlDbType.NChar,40,cooperateInfo.Area),
                                      DbHelper.MakeInParam("@Contact",(DbType)SqlDbType.NChar,10,cooperateInfo.Contact),
                                      DbHelper.MakeInParam("@Phone",(DbType)SqlDbType.NVarChar,40,cooperateInfo.Phone),
                                      DbHelper.MakeInParam("@Agency",(DbType)SqlDbType.NChar,10,cooperateInfo.Agency),
                                      DbHelper.MakeInParam("@Audit",(DbType)SqlDbType.Bit,4,cooperateInfo.Audit),
                                      DbHelper.MakeInParam("@Maturity",(DbType)SqlDbType.NChar,cooperateInfo.Maturity.Length,cooperateInfo.Maturity),
                                      DbHelper.MakeInParam("@Date",(DbType)SqlDbType.NChar,cooperateInfo.Date.Length,cooperateInfo.Date)
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("UPDATE [ZhangBY].[dbo].[{0}cooperate] ", BaseConfigs.GetTablePrefix);
            commantText.Append("SET[title]=@title,[projectname]=@projectname,[projecttype]=@projecttype,[key]=@key,[description]=@description,");
            commantText.Append("[phone]=@phone,[contact]=@contact,[area]=@area,[Agency]=@agency,[audit]=@audit,[maturity]=@maturity,[date]=@date");
            commantText.Append(" WHERE [projectid]=@projectid AND[userid]=@userid");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }

        /// <summary>
        /// 根据合作项目更新合作信息
        /// </summary>
        /// <param name="cooperateInfo"></param>
        /// <returns></returns>
        public int UpdateCooperateByProjectId(CooperateInfo cooperateInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@ProjectId",(DbType)SqlDbType.NVarChar,60,cooperateInfo.ProjectId),
                                      DbHelper.MakeInParam("@ProjectName",(DbType)SqlDbType.NChar,20,cooperateInfo.ProjectName),
                                      DbHelper.MakeInParam("@ProjectType",(DbType)SqlDbType.NChar,10,cooperateInfo.ProjectType),
                                      DbHelper.MakeInParam("@Key",(DbType)SqlDbType.NVarChar,100,cooperateInfo.Key),
                                      DbHelper.MakeInParam("@Title",(DbType)SqlDbType.NChar,30,cooperateInfo.Title),
                                      DbHelper.MakeInParam("@Description",(DbType)SqlDbType.NVarChar,200,cooperateInfo.Description),                                     
                                      DbHelper.MakeInParam("@Area",(DbType)SqlDbType.NChar,40,cooperateInfo.Area),
                                      DbHelper.MakeInParam("@Contact",(DbType)SqlDbType.NChar,10,cooperateInfo.Contact),
                                      DbHelper.MakeInParam("@Phone",(DbType)SqlDbType.NVarChar,40,cooperateInfo.Phone),
                                      DbHelper.MakeInParam("@Agency",(DbType)SqlDbType.NChar,10,cooperateInfo.Agency),
                                      DbHelper.MakeInParam("@Audit",(DbType)SqlDbType.Bit,4,cooperateInfo.Audit),
                                      DbHelper.MakeInParam("@Maturity",(DbType)SqlDbType.NChar,cooperateInfo.Maturity.Length,cooperateInfo.Maturity),
                                      DbHelper.MakeInParam("@Date",(DbType)SqlDbType.NChar,cooperateInfo.Date.Length,cooperateInfo.Date)
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("UPDATE [ZhangBY].[dbo].[{0}cooperate] ", BaseConfigs.GetTablePrefix);
            commantText.Append("SET[title]=@title,[projectname]=@projectname,[projecttype]=@projecttype,[key]=@key,[description]=@description,");
            commantText.Append("[phone]=@phone,[contact]=@contact,[area]=@area,[Agency]=@agency,[audit]=@audit,[maturity]=@maturity,[date]=@date");
            commantText.Append(" WHERE [projectid]=@projectid ");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
 
        /// <summary>
        /// 根据USERID和合作项目查找合作信息
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="proid"></param>
        /// <returns></returns>
        public DataTable GetCooperateByProjectIdAndUserId(string userid, string projectid)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@projectid",(DbType)SqlDbType.NVarChar,60,projectid)
                                  };
            string commandText = string.Format("SELECT * FROM [dbo].[{0}cooperate] WHERE [userid]=@userid AND [projectid]=@projectid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, parms).Tables[0];
        }
        /// <summary>
        /// 根据合作项目查找合作信息
        /// </summary>
        /// <param name="proid"></param>
        /// <returns></returns>
        public DataTable GetCooperateByProjectId(string projectid)
        {
            DbParameter parms = DbHelper.MakeInParam("@projectid", (DbType)SqlDbType.NVarChar, 60, projectid);
            string commandText = string.Format("SELECT * FROM [dbo].[{0}cooperate] WHERE [projectid]=@projectid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, parms).Tables[0];
        }
        /// <summary>
        /// 根据USERID读取所有合作数据行
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDataReader GetCooperateByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT * FROM [dbo].[{0}cooperate] WHERE [userid]=@userid ORDER BY [date] DESC", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteReader(CommandType.Text, commandText, parms);
        }

        /// <summary>
        /// 根据USERID和合作项目删除合作信息
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="proid"></param>
        /// <returns></returns>
        public int DelCooperateByUserIdAndProjectId(string userid, string projectid)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@projectid",(DbType)SqlDbType.NVarChar,60,projectid)
                                  };
            string commandText = string.Format("DELETE FROM [dbo].[{0}cooperate] WHERE [userid]=@userid AND [projectid]=@projectid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 根据合作项目删除合作信息
        /// </summary>
        /// <param name="proid"></param>
        /// <returns></returns>
        public int DelCooperateByProjectId(string projectid)
        {
            DbParameter parms = DbHelper.MakeInParam("@projectid", (DbType)SqlDbType.NVarChar, 60, projectid);
            string commandText = string.Format("DELETE FROM [dbo].[{0}cooperate] WHERE [projectid]=@projectid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 根据用户ID取得合作产品总行数
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int GetCountCooperateByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT COUNT(title) FROM [dbo].[{0}cooperate] WHERE [userid]=@userid", BaseConfigs.GetTablePrefix);
            return Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.Text, commandText, parms), 0);
        }
    }
}
